package com.fcscs.cservice.dao.hibernate;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.fcscs.core.dao.ConnectionManager;
import com.fcscs.core.domain.BaseEntity.DBLoc;
import com.fcscs.cservice.dao.InRoomConfDao;
import com.fcscs.cservice.domain.BillOption;
import com.fcscs.cservice.domain.ChargeCategory;
import com.fcscs.cservice.domain.ChargeCategoryType;
import com.fcscs.cservice.domain.ChargeType;
import com.fcscs.cservice.domain.TransactionType;
import com.fcscs.cservice.domain.model.BillOptionView;
import com.fcscs.cservice.domain.model.ChargeCategoryTypeView;
import com.fcscs.cservice.domain.model.ChargeCategoryView;
import com.fcscs.cservice.domain.model.ChargeTypeView;
import com.fcscs.cservice.domain.model.TransactionTypeView;


@SuppressWarnings("unchecked")
@Repository("inRoomConfDao")
public class InRoomConfDaoHibernate implements InRoomConfDao {

	@Autowired
	private ConnectionManager connMgr;

	private static DBLoc DBLoc = new TransactionType().getDBLoc();

	private static final String FIND_TRANSACTION_TYPE = "FROM TransactionType tt Where tt.orgId=:orgId and tt.propertyId=:propId ";

	private static final String FIND_CHARGETYPE = "FROM ChargeType ct where ct.orgId=:orgId and ct.propertyId=:propId ";

	private static final String FIND_BILLOPTION = "FROM BillOption ct where ct.orgId=:orgId and ct.propertyId=:propId ";

	private static final String TRANSACTIONTYPE_CODE_CNT  = "select count(*) from transaction_type where org_id = :orgId and " +
	" property_id = :propId and code = :code and id <> :id and active = true ";

	private static final String CHARGETYPE_CODE_CNT = "select count(*) from charge_type where org_id = :orgId and " +
	" property_id = :propId and code = :code and id <> :id and active = true ";

	private static final String CHARGE_CATEGORY_CODE_CNT = "select count(*) from charge_category where org_id = :orgId and " +
	" property_id = :propId and code = :code and id <> :id and active = true ";

	private static final String BILLOPTION_NAME_CNT = "select count(*) from bill_option where org_id = :orgId and " +
	" property_id = :propId and name = :name and id <> :id and active = true ";

	private static final String FIND_TRANSACTIONTYPE_VIEW ="select tt.id as transactionTypeId, tt.name as name, tt.name_sec_lang as nameSecLang, " +
	"tt.name_thd_lang as nameThdLang, tt.code as code, tt.is_revenue as isRevenue " +
	" from transaction_type tt where tt.org_id=:orgId and tt.property_id=:propId";

	private static  final String FIND_CHARGETYPE_VIEW = "select ct.id as chargeTypeId, ct.name as name, ct.name_sec_lang as nameSecLang, ct.name_thd_lang as nameThdLang, " +
	"ct.code as code, ct.transaction_type_id as transactionTypeId, tt.code as transactionTypeCode, ct.is_service_type as isServiceType " +
	"from fcs_guest.charge_type ct join fcs_guest.transaction_type tt on ct.transaction_type_id=tt.id " +
	"where ct.org_id=:orgId and ct.property_id=:propId and ct.active=true and tt.active=true";

	private static final String FIND_BILL_OPTION_VIEW = "select bo.id as billOptionId, bo.name as name, bo.bill_type as type, bo.code as code " +
	"from fcs_guest.bill_option bo where bo.org_id=:orgId and bo.property_id=:propId ";

	
	private static final String FIND_CHARGE_CATEGORY_VIEW = "FROM ChargeCategory cc WHERE cc.orgId=:orgId and cc.propertyId=:propId";
	
	private static final String FIND_CHARGE_CATEGORY_TYPE_VIEW = "SELECT  cct.id as chargeCategoryTypeId," +
	"cct.charge_category_id as chargeCategoryId," +
	"cct.charge_type_id as chargeTypeId," +
	"ct.code as chargeTypeCode," +
	"ct.transaction_type_id as transactionTypeId," +
	"tt.code as transactionTypeCode," +
	"cct.charge_by_type as chargeByType," +
	"cct.charge_value as value," +
	"cct.enabled as enable," +
	"cct.code as code," +
	"cct.name as name " +
	"FROM charge_category_type cct " +
	"LEFT OUTER JOIN charge_type ct ON cct.charge_type_id = ct.id " +
	"LEFT OUTER JOIN transaction_type tt ON ct.transaction_type_id = tt.id " +
	"WHERE cct.org_id =:orgId and cct.property_id =:propId and cct.charge_category_id =:chargeCategoryId";



	@Override
	public List<TransactionType> getTransactionTypeList(long orgId, long propId) {

		Query q = connMgr.getCurrentSession(DBLoc).createQuery(FIND_TRANSACTION_TYPE);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		return q.list();
	}


	@Override
	public List<TransactionTypeView> getTransactionTypeViewList(long orgId,long propId) {

		SQLQuery sqlQu =  connMgr.getCurrentSession(DBLoc).createSQLQuery(FIND_TRANSACTIONTYPE_VIEW);
		sqlQu.setLong("orgId", orgId);
		sqlQu.setParameter("propId", propId, Hibernate.LONG);

		sqlQu.addScalar("transactionTypeId", Hibernate.LONG).
		addScalar("name", Hibernate.STRING).
		addScalar("nameSecLang", Hibernate.STRING).
		addScalar("nameThdLang", Hibernate.STRING).
		addScalar("code", Hibernate.STRING).
		addScalar("isRevenue",Hibernate.BOOLEAN);

		return sqlQu.setResultTransformer(Transformers.aliasToBean(TransactionTypeView.class)).list();
	}

	@Override
	public TransactionType createTransactionType(TransactionType txType) {
		connMgr.getCurrentSession(DBLoc).save(txType);
		return txType;
	}

	@Override
	public TransactionType saveTransactionType(TransactionType txType) {
		connMgr.getCurrentSession(DBLoc).saveOrUpdate(txType);
		return txType;
	}

	@Override
	public boolean deleteTransactionType(TransactionType txType) {
		String sql = "Update TransactionType sd set sd.active = false where sd.id=:id";
		Query q = connMgr.getCurrentSession(DBLoc).createQuery(sql);
		q.setLong("id", txType.getId());
		
		return (q.executeUpdate()== 1);
	}

	@Override
	public boolean permDeleteTransactionType(TransactionType txType) {

		try {
			connMgr.getCurrentSession(DBLoc).delete(txType);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	@Override
	public boolean isTransactionTypeTaken(long orgId, long propId, String code,Long id) {

		Query q  = connMgr.getCurrentSession(DBLoc).createSQLQuery(TRANSACTIONTYPE_CODE_CNT);
		q.setString("code", code);
		q.setLong("id", id == null? -1 : id);
		q.setLong("orgId", orgId);
		q.setParameter("propId", propId, Hibernate.LONG);

		return ((Number) q.uniqueResult()).intValue() > 0;
	}

	@Override
	public List<ChargeType> getChargeTypeList(long orgId, long propId) {

		Query q = connMgr.getCurrentSession(DBLoc).createQuery(FIND_CHARGETYPE);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);

		return q.list();
	}

	@Override
	public List<ChargeTypeView> getChargeTypeViewList(long orgId, long propId) {

		//TODO unit test
		SQLQuery sqlQu =  connMgr.getCurrentSession(DBLoc).createSQLQuery(FIND_CHARGETYPE_VIEW);
		sqlQu.setLong("orgId", orgId);
		sqlQu.setParameter("propId", propId, Hibernate.LONG);

		sqlQu.addScalar("chargeTypeId", Hibernate.LONG).
		addScalar("name", Hibernate.STRING).
		addScalar("nameSecLang", Hibernate.STRING).
		addScalar("nameThdLang", Hibernate.STRING).
		addScalar("code", Hibernate.STRING).
		addScalar("transactionTypeId", Hibernate.LONG).
		addScalar("transactionTypeCode", Hibernate.STRING).
		addScalar("isServiceType",Hibernate.BOOLEAN);

		return sqlQu.setResultTransformer(Transformers.aliasToBean(ChargeTypeView.class)).list();
	}

	@Override
	public ChargeType createChargeType(ChargeType chargeType) {
		connMgr.getCurrentSession(DBLoc).save(chargeType);
		return chargeType;
	}

	@Override
	public ChargeType saveChargeType(ChargeType chargeType) {
		connMgr.getCurrentSession(DBLoc).saveOrUpdate(chargeType);
		return chargeType;
	}

	@Override
	public boolean deleteChargeType(ChargeType chargeType) {
		String sql = "Update ChargeType sd set sd.active = false where sd.id=:id";
		Query q = connMgr.getCurrentSession(DBLoc).createQuery(sql);
		q.setLong("id", chargeType.getId());

		return (q.executeUpdate()== 1);
	}

	@Override
	public boolean permDeleteChargeType(ChargeType chargeType) {
		try {
			connMgr.getCurrentSession(DBLoc).delete(chargeType);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	@Override
	public boolean isChargeTypeTaken(long orgId, long propId, String code,Long id) {

		Query q  = connMgr.getCurrentSession(DBLoc).createSQLQuery(CHARGETYPE_CODE_CNT);
		q.setString("code", code);
		q.setLong("id", id == null? -1 : id);
		q.setLong("orgId", orgId);
		q.setParameter("propId", propId, Hibernate.LONG);

		return ((Number) q.uniqueResult()).intValue() > 0;
	}

	@Override
	public List<ChargeCategoryView> getChargeCategoryViewList(long orgId,long propId) {
		
		Query q = connMgr.getCurrentSession(DBLoc).createQuery(FIND_CHARGE_CATEGORY_VIEW);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		List<ChargeCategory> ccList = q.list();
		List<ChargeCategoryView> list = new ArrayList<ChargeCategoryView>();
		for(ChargeCategory cc : ccList){
			ChargeCategoryView ccv = new ChargeCategoryView();
			ccv.setChargeCategoryId(cc.getId());
			ccv.setCode(cc.getCode());
			ccv.setName(cc.getName());
			ccv.setNameSecLang(cc.getNameSecLang());
			ccv.setNameThdLang(cc.getNameThdLang());
			ccv.setChargeCategoryTypeList(findChargeCategoryTypeView(orgId, propId, cc.getId()));
			list.add(ccv);
		}

		return list;
	}

	private List<ChargeCategoryTypeView> findChargeCategoryTypeView(long orgId, long propId, long categoryId) {
		
		SQLQuery sqlQu =  connMgr.getCurrentSession(DBLoc).createSQLQuery(FIND_CHARGE_CATEGORY_TYPE_VIEW);
		sqlQu.setLong("orgId", orgId);
		sqlQu.setParameter("propId", propId, Hibernate.LONG);
		sqlQu.setParameter("chargeCategoryId", categoryId);
		sqlQu.addScalar("chargeCategoryTypeId", Hibernate.LONG).
		addScalar("chargeCategoryId", Hibernate.LONG).
		addScalar("chargeTypeId", Hibernate.LONG).
		addScalar("chargeTypeCode", Hibernate.STRING).
		addScalar("transactionTypeId", Hibernate.LONG).
		addScalar("transactionTypeCode", Hibernate.STRING).
		addScalar("chargeByType", Hibernate.STRING).
		addScalar("value", Hibernate.BIG_DECIMAL).
		addScalar("enable", Hibernate.BOOLEAN).
		addScalar("code", Hibernate.STRING).
		addScalar("name", Hibernate.STRING);
		
		return sqlQu.setResultTransformer(Transformers.aliasToBean(ChargeCategoryTypeView.class)).list();
	}

	@Override
	public ChargeCategoryView createChargeCategoryView(long orgId, long propId,ChargeCategoryView chargeCategory) {
		
		ChargeCategory ccgor = new ChargeCategory();
		ccgor.setName(chargeCategory.getName());
		ccgor.setNameSecLang(chargeCategory.getNameSecLang());
		ccgor.setNameThdLang(chargeCategory.getNameThdLang());
		ccgor.setCode(chargeCategory.getCode());
		ccgor.setOrgId(orgId);
		ccgor.setPropertyId(propId);
		
		connMgr.getCurrentSession(DBLoc).save(ccgor);
		
		List<ChargeCategoryTypeView> list = chargeCategory.getChargeCategoryTypeList();
		for(ChargeCategoryTypeView cctv : list){
			ChargeCategoryType cct = new ChargeCategoryType();
			cct.setChargeCategoryId(cctv.getChargeCategoryId());
			cct.setChargeTypeId(cctv.getChargeTypeId());
			cct.setName(cctv.getName());
			cct.setCode(cctv.getCode());
			cct.setChargeByType(cctv.getChargeByType());
			cct.setChargeValue(cctv.getValue());
			cct.setEnabled(cctv.getEnable());
			cct.setOrgId(orgId);
			cct.setPropertyId(propId);
			cct.setPerType("hello per type");
			connMgr.getCurrentSession(DBLoc).save(cct);
		}
		
		return chargeCategory;
	}

	@Override
	public ChargeCategoryView saveChargeCategoryView(long orgId, long propId,ChargeCategoryView chargeCategory) {
		
		ChargeCategory ccgor = new ChargeCategory();
		ccgor.setName(chargeCategory.getName());
		ccgor.setNameSecLang(chargeCategory.getNameSecLang());
		ccgor.setNameThdLang(chargeCategory.getNameThdLang());
		ccgor.setCode(chargeCategory.getCode());
		ccgor.setOrgId(orgId);
		ccgor.setPropertyId(propId);
		
		connMgr.getCurrentSession(DBLoc).saveOrUpdate(ccgor);
		
		List<ChargeCategoryTypeView> list = chargeCategory.getChargeCategoryTypeList();
		for(ChargeCategoryTypeView cctv : list){
			ChargeCategoryType cct = new ChargeCategoryType();
			cct.setChargeCategoryId(cctv.getChargeCategoryId());
			cct.setChargeTypeId(cctv.getChargeTypeId());
			cct.setName(cctv.getName());
			cct.setCode(cctv.getCode());
			cct.setChargeByType(cctv.getChargeByType());
			cct.setChargeValue(cctv.getValue());
			cct.setEnabled(cctv.getEnable());
			cct.setPerType("hell pertype");
			cct.setOrgId(orgId);
			cct.setPropertyId(propId);
			
			connMgr.getCurrentSession(DBLoc).saveOrUpdate(cct);
		}
		
		return chargeCategory;
	}

	@Override
	public boolean deleteChargeCategoryView(ChargeCategoryView chargeCategory) {
		
		try {
			Query q1 = connMgr.getCurrentSession(DBLoc).createQuery("Update ChargeCategory cc set cc.active=false where cc.id=:id");
			q1.setLong("id", chargeCategory.getChargeCategoryId());
			q1.executeUpdate();
			System.out.println((q1.executeUpdate()==1)+"----------------------------------------1------"+ chargeCategory.getChargeCategoryId());
			List<ChargeCategoryTypeView> list = chargeCategory.getChargeCategoryTypeList();
			for (ChargeCategoryTypeView cct : list) {
				Query q = connMgr.getCurrentSession(DBLoc).createQuery(
								"Update ChargeCategoryType cct set cct.active=false where cct.id=:id");
				q.setLong("id", cct.getChargeCategoryTypeId());
				q.executeUpdate();
				System.out.println((q.executeUpdate()==1)+"---------------------2-------------------"+ cct.getChargeCategoryTypeId());
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean permDeleteChargeCategory(ChargeCategoryView chargeCategory) {
		try {
			Session session = connMgr.getCurrentSession(DBLoc);
			SQLQuery sql = session.createSQLQuery("delete from charge_category where id=:id");
			sql.setParameter("id", chargeCategory.getChargeCategoryId());
			sql.addEntity(ChargeCategory.class);
			sql.executeUpdate();
			
			System.out.println(chargeCategory.getChargeCategoryId()+"------------------------------------------3----------------");
			
			List<ChargeCategoryTypeView> list = chargeCategory.getChargeCategoryTypeList();
			for (ChargeCategoryTypeView cctv : list) {
				SQLQuery q = session.createSQLQuery("delete from Charge_category_type where id=:id");
				q.setParameter("id",cctv.getChargeCategoryTypeId());
				q.addEntity(ChargeCategoryType.class);
				q.executeUpdate();
				System.out.println(cctv.getChargeCategoryTypeId()+"---------------------------------------------4----------------");
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean isChargeCategoryTaken(long orgId, long propId, String code,Long id) {

		Query q  = connMgr.getCurrentSession(DBLoc).createSQLQuery(CHARGE_CATEGORY_CODE_CNT);
		q.setString("code", code);
		q.setLong("id", id == null? -1 : id);
		q.setLong("orgId", orgId);
		q.setParameter("propId", propId, Hibernate.LONG);

		return ((Number) q.uniqueResult()).intValue() > 0;
	}

	@Override
	public List<BillOption> getBillOptionList(long orgId, long propId) {

		Query q = connMgr.getCurrentSession(DBLoc).createQuery(FIND_BILLOPTION);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);

		return q.list();
	}

	@Override
	public List<BillOptionView> getBillOptionViewList(long orgId, long propId) {

		//TODO unit test
		SQLQuery sqlQu =  connMgr.getCurrentSession(DBLoc).createSQLQuery(FIND_BILL_OPTION_VIEW);
		sqlQu.setLong("orgId", orgId);
		sqlQu.setParameter("propId", propId, Hibernate.LONG);

		sqlQu.addScalar("billOptionId", Hibernate.LONG).
		addScalar("name", Hibernate.STRING).
		addScalar("type", Hibernate.STRING).
		addScalar("code", Hibernate.STRING);

		return sqlQu.setResultTransformer(Transformers.aliasToBean(BillOptionView.class)).list();
	}

	@Override
	public BillOption createBillOption(BillOption bo) {
		connMgr.getCurrentSession(DBLoc).save(bo);
		return bo;
	}

	@Override
	public BillOption saveBillOption(BillOption bo) {
		connMgr.getCurrentSession(DBLoc).saveOrUpdate(bo);

		return bo;
	}

	@Override
	public boolean deleteBillOption(BillOption bo) {
		String sql = "Update BillOption sd set sd.active = false where sd.id=:id";
		Query q = connMgr.getCurrentSession(DBLoc).createQuery(sql);
		q.setLong("id", bo.getId());
		
		return (q.executeUpdate()== 1);
	}

	@Override
	public boolean permDeleteBillOption(BillOption bo) {
		try {
			connMgr.getCurrentSession(DBLoc).delete(bo);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	@Override
	public boolean isBillOptionTaken(long orgId, long propId, String name,Long id) {

		Query q  = connMgr.getCurrentSession(DBLoc).createSQLQuery(BILLOPTION_NAME_CNT);
		q.setString("name", name);
		q.setLong("id", id == null? -1 : id);
		q.setLong("orgId", orgId);
		q.setParameter("propId", propId, Hibernate.LONG);

		return ((Number) q.uniqueResult()).intValue() > 0;
	}

	@Override
	public BillOption getBillOption(long id) {
		return (BillOption) connMgr.getCurrentSession(DBLoc).get(BillOption.class, id);
	}

}
